/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nsc.home;

import com.absisgroup.nsc.utilities.GenericServlet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 *
 * @author Hue
 */
public class LoadQueryById extends GenericServlet {

    @Override
    public int process(final Connection con, final HttpServletRequest request, final HttpServletResponse response) throws Exception {
        final String nquePk = request.getParameter("nquePk");
        writeToResponse(response, toJSONObject(con, nquePk));
        
        return DONT_RUN_JSP;
    }
    
    private String toJSONObject(final Connection con, final String nquePk) throws Exception {
        final JSONObject jsObj = new JSONObject();
        jsObj.put("success", true);
        jsObj.put("msg", "");
        final JSONObject jsData = new JSONObject();
        final JSONObject jsQuery = new JSONObject();
        final JSONObject parameters = new JSONObject();
        final JSONArray params = new JSONArray();
        //Query [
        String strQuery = "select NQUE_CREATOR, NQUE_CODE, NQUE_NAME, NQUE_DESCRIPTION, NQUE_ISREADONLY, NQUE_ISADMIN, NQUE_STATEMENT " + 
                          "from nsc_query where NQUE_PK = ? ";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1, nquePk);
        ResultSet rs = pStmt.executeQuery();
        if(rs.next()) {
            jsQuery.put("nquePk", nquePk);
            jsQuery.put("statement", rs.getString("NQUE_STATEMENT"));
            jsQuery.put("isautosaved", 0);
            jsQuery.put("isreadonly", rs.getInt("NQUE_ISREADONLY"));
            jsQuery.put("code", rs.getString("NQUE_CODE"));
            jsQuery.put("name", rs.getString("NQUE_NAME"));
            jsQuery.put("comment", rs.getString("NQUE_DESCRIPTION"));
            jsQuery.put("isadmin", rs.getInt("NQUE_ISADMIN"));
            jsQuery.put("creator", rs.getString("NQUE_CREATOR"));
        }
        pStmt.close();
        //]
        //Parameters [
        strQuery = "select NQUEP_CODE, NQUEP_NAME, NQUEP_SORTORDER, " + 
                   "       decode(NQUEP_DATATYPE,1,'STRING',2,'NUMBER',3,'DECIMAL',4,'DATE',5,'DATETIME','STRING') DATATYPE " +
                   "from nsc_query_parameter where nquep_nque_fk = ? " + 
                   "order by NQUEP_SORTORDER";
        pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1, nquePk);
        rs = pStmt.executeQuery();
        while(rs.next()) {
            final JSONObject p = new JSONObject();
            p.put("code", rs.getString("NQUEP_CODE"));
            p.put("name", rs.getString("NQUEP_NAME"));
            p.put("datatype", rs.getString("DATATYPE"));
            p.put("sortorder", rs.getInt("NQUEP_SORTORDER"));
            params.put(p);
        }
        pStmt.close();
        //]
        parameters.put("parameter", params);
        jsQuery.put("parameters", parameters);
        jsData.put("query", jsQuery);
        jsObj.put("data", jsData);

        return jsObj.toString();
    }
}
